import pandas as pd
import pymysql

data=pd.read_csv('result.csv',na_values='NULL')
rows_num=data.shape[0]
db=pymysql.connect(host="localhost",user="root",password="123456",db="PyDataBase",charset="utf8")

cursor=db.cursor()

#基本使用
def print_colnames():
    cursor.execute("SHOW COLUMNS FROM py_Create;")
    col_names=cursor.fetchall()
    print(col_names)
    return  col_names

def print_alldata():
    cursor.execute("SELECT * FROM PY_CREATE;")
    data=cursor.fetchall()
    print("ALL data:",data)
    return data

if __name__ == '__main__':
    print(data)
    '''
    cursor.execute("CREATE TABLE DOUBAN_BOOK("
                   "img_urls VARCHAR (100), "
                   "titles VARCHAR (100),"
                   "ratings VARCHAR (20),"
                   "authors VARCHAR (100),"
                   "details VARCHAR (200));")
    '''

    for i in range(rows_num):
        sql = "INSERT INTO DOUBAN_BOOK (img_urls, titles,ratings, authors, details) VALUES (%s,%s,%s,%s,%s);"
        #为了防止多次访问，这里放在csv文件
        #print(data.iloc[i, 0], data.iloc[i, 1],
                  #          data.iloc[i, 2], data.iloc[i, 3], data.iloc[i, 4])
        #data.iloc
        #cursor.execute(sql, (data.ix[i, :][0], data.ix[i, :][1],
        #                   data.ix[i, :][2], data.ix[i, :][3], data.ix[i, :][4]))
        cursor.execute(sql, ((str)(data.iloc[i, 0]), (str)(data.iloc[i, 1]),(str)(data.iloc[i, 2]), (str)(data.iloc[i, 3]), (str)(data.iloc[i, 4])))

    db.commit()